package hc

import (
	"bytes"
	"database/sql"
	"fmt"
	"regexp"
	"strings"
	"text/template"
	"time"

	"github.com/jmoiron/sqlx"
)

// ErrNoRows sql的ErrNoRows
var ErrNoRows = sql.ErrNoRows

// SQLM SQLM
type SQLM struct {
	*sqlx.DB
}

// DB 数据库连接
var DB SQLM

// DBConn 连接数据库
func DBConn(driverName, dataSourceName string) (err error) {
	DB.DB, err = sqlx.Open(driverName, dataSourceName)
	if err != nil {
		err = fmt.Errorf("dsn:%s, err: %s", dataSourceName, err)
		return
	}
	err = DB.DB.Ping()
	if err != nil {
		err = fmt.Errorf("dsn:%s, err: %s", dataSourceName, err)
		return
	}
	// 用于设置最大打开的连接数，值<=0表示不限制,默认值为0
	// DB.DB.SetMaxOpenConns(0)

	// 设置闲置的连接数, 默认是2
	// 值<=0，表示不使用空闲连接池，即一个连接如果不使用，不会放入空闲连接池。因此，这种方式不会复用连接，每次执行SQL语句，都会重新建立新的连接
	// 如果MaxIdleConns 大于0，且大于MaxOpenConns，那么将调整MaxIdleConns等于MaxOpenConns，有多余的连接时会关闭多余的连接。
	DB.DB.SetMaxIdleConns(10)

	// 设置数据库闲置链接超时时间
	DB.DB.SetConnMaxLifetime(time.Second * 10)

	return
}

// getSQL 获取sql
func (s SQLM) getSQL(pattern string, arg interface{}) (query string, args []interface{}, err error) {
	t := template.New("new template")
	t = t.Funcs(template.FuncMap{"notNil": notNil})
	t, err = t.Parse(pattern)
	if err != nil {
		err = fmt.Errorf("template parse error: %s", err.Error())
		return
	}
	var buf bytes.Buffer
	err = t.Execute(&buf, arg)
	if err != nil {
		err = fmt.Errorf("template execute error: %s", err.Error())
		return
	}
	query, args, err = sqlx.Named(buf.String(), arg)
	if err != nil {
		return
	}
	query, args, err = sqlx.In(query, args...)
	if err != nil {
		return
	}

	query = s.Rebind(query)
	r1 := regexp.MustCompile(`[\s]+`)
	query = r1.ReplaceAllString(query, " ")
	query = strings.TrimSpace(query)
	return
}

// Query 执行一次多行查询
func (s SQLM) Query(pattern string, arg interface{}) (rows *sqlx.Rows, err error) {
	query, args, err := s.getSQL(pattern, arg)
	if err != nil {
		return
	}
	stmt, err := s.Preparex(query)
	if err != nil {
		err = fmt.Errorf(`%v, query:%s, params: %v`, err, query, args)
		return
	}
	defer stmt.Close()
	rows, err = stmt.Queryx(args...)
	if err != nil {
		err = fmt.Errorf(`%v, query:%s, params: %v`, err, query, args)
	}
	return
}

// QueryRow 执行一次单行查询
func (s SQLM) QueryRow(pattern string, arg interface{}) (row *sqlx.Row, err error) {
	query, args, err := s.getSQL(pattern, arg)
	if err != nil {
		return
	}
	stmt, err := s.Preparex(query)
	if err != nil {
		err = fmt.Errorf(`%v, query:%s, params: %v`, err, query, args)
		return
	}
	defer stmt.Close()
	row = stmt.QueryRowx(args...)
	return
}

// Exec 执行一次数据操作
func (s SQLM) Exec(pattern string, arg interface{}) (result sql.Result, err error) {
	query, args, err := s.getSQL(pattern, arg)
	if err != nil {
		return
	}
	stmt, err := s.Preparex(query)
	if err != nil {
		err = fmt.Errorf(`%v, exec:%s, params: %v`, err, query, args)
		return
	}
	defer stmt.Close()
	result, err = stmt.Exec(args...)
	if err != nil {
		err = fmt.Errorf(`%v, exec:%s, params: %v`, err, query, args)
	}
	return
}

// Tx sqlt事务对象
type Tx struct {
	*sqlx.Tx
}

// Begin 开始事务
func (s SQLM) Begin() (tx Tx, err error) {
	tx.Tx, err = s.Beginx()
	if err != nil {
		err = fmt.Errorf("开始事务失败:%v", err)
	}
	return
}

// Rollback 回滚事务
func (tx Tx) Rollback() (err error) {
	err = tx.Tx.Rollback()
	if err != nil {
		err = fmt.Errorf("回滚事务失败:%v", err)
	}
	return
}

// Commit 提交事务
func (tx Tx) Commit() (err error) {
	err = tx.Tx.Commit()
	if err != nil {
		err = fmt.Errorf("回滚事务失败:%v", err)
	}
	return
}

// getSQL 获取sql
func (tx Tx) getSQL(pattern string, arg interface{}) (query string, args []interface{}, err error) {
	t := template.New("new template")
	t, err = t.Parse(pattern)
	if err != nil {
		err = fmt.Errorf("template parse error: %s", err.Error())
		return
	}
	var buf bytes.Buffer
	err = t.Execute(&buf, arg)
	if err != nil {
		err = fmt.Errorf("template execute error: %s", err.Error())
		return
	}
	query, args, err = sqlx.Named(buf.String(), arg)
	if err != nil {
		return
	}
	query, args, err = sqlx.In(query, args...)
	if err != nil {
		return
	}

	query = tx.Rebind(query)
	r1 := regexp.MustCompile(`[\s]+`)
	query = r1.ReplaceAllString(query, " ")
	query = strings.TrimSpace(query)
	return
}

// Query 在事务中执行一次多行查询
func (tx Tx) Query(pattern string, arg interface{}) (rows *sqlx.Rows, err error) {
	query, args, err := tx.getSQL(pattern, arg)
	if err != nil {
		return
	}
	stmt, err := tx.Preparex(query)
	if err != nil {
		err = fmt.Errorf(`%v, query:%s, params: %v`, err, query, args)
		return
	}
	defer stmt.Close()
	rows, err = stmt.Queryx(args...)
	if err != nil {
		err = fmt.Errorf(`%v, query:%s, params: %v`, err, query, args)
	}
	return
}

// QueryRow 在事务中执行一次单行查询
func (tx Tx) QueryRow(pattern string, arg interface{}) (row *sqlx.Row, err error) {
	query, args, err := tx.getSQL(pattern, arg)
	if err != nil {
		return
	}
	stmt, err := tx.Preparex(query)
	if err != nil {
		err = fmt.Errorf(`%v, query:%s, params: %v`, err, query, args)
		return
	}
	defer stmt.Close()
	row = stmt.QueryRowx(args...)
	return
}

// Exec 在事务中执行一次数据操作
func (tx Tx) Exec(pattern string, arg interface{}) (result sql.Result, err error) {
	query, args, err := tx.getSQL(pattern, arg)
	if err != nil {
		return
	}
	stmt, err := tx.Preparex(query)
	if err != nil {
		err = fmt.Errorf(`%v, exec:%s, params: %v`, err, query, args)
		return
	}
	defer stmt.Close()
	result, err = stmt.Exec(args...)
	if err != nil {
		err = fmt.Errorf(`%v, exec:%s, params: %v`, err, query, args)
	}
	return
}

func notNil(t interface{}) bool {
	return t != nil
}
